import logging

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_dist_router = APIRouter(prefix="/data/area", tags=["数据字典接口"], dependencies=[Depends(oauth2_scheme)])


class DistSearch(BaseModel):
    flag: str = Field("", description="")
    arg1: str = Field("", description="")
    arg2: str = Field("", description="")


@data_dist_router.get("/getList", summary="数据字典接口")
def getList(dist: DistSearch = Query(), db: Session = Depends(get_db)):
    try:
        flag = dist.flag
        arg1 = dist.arg1
        arg2 = dist.arg2
        with db.begin():
            param = {"flag": flag, "arg1": arg1, "arg2": arg2}
            if (flag == "1"):
                sql = "select fdictno,fdictname from s_ts_equipment_dict where 1=1 order by fdictno"
            if (flag == "2"):
                sql = "select fclassstno,fclassstname from s_ts_equipment_class where 1=1 order by fclassstno"
            if (flag == "6"):  # 客户供应商
                sql = "select fsupno,fsupname from s_ts_supplier where 1=1"
                if arg1 != "":
                    sql = sql + " and fsite_no=:arg1"
                sql = sql + " order by fsupno"
            if (flag == "7"):  # 风险等级
                sql = "select flevelno as value,flevelscre as label from s_ts_danger_level where 1=1 order by flevelno"
            if (flag == "10"):  # 客户实验室
                sql = "select fpoint_no,fpoint_name from a_ts_point where fpoint_flag=0 "
                if arg1 != "":
                    sql = sql + " and fsite_no=:arg1"
                sql = sql + " order by fpoint_no"
            if (flag == "11"):  # 客户室外
                sql = "select fpoint_no,fpoint_name from a_ts_point where fpoint_flag=1 "
                if arg1 != "":
                    sql = sql + " and fsite_no=:arg1"
                sql = sql + " order by fpoint_no"
            if (flag == "13"):  # 客户所属用户
                sql = "select id,username from am_sys_user where status=1 "
                if arg1 != "":
                    sql = sql + " and fsite_no_belong=:arg1"
                sql = sql + " order by id"
            if (flag == "14"):  # 设备资料
                sql = "select fequipmentno,fequipmentname from s_ts_equipment_info where 1=1  order by fequipmentno"
            if (flag == "15"):  # 巡查类型
                sql = "select fchecktypeno,fcheckname from s_ts_check_type where 1=1  order by fchecktypeno"
            if (flag == "16"):  # 保养类型
                sql = "select fmmtypeno,fmmtypename from s_ts_maintain_type where 1=1  order by fmmtypeno"
            if (flag == "17"):  # 保养级别
                sql = "select fmmlevelno,fmmlevelname from s_ts_maintain_level where 1=1  order by fmmlevelno"
            if (flag == "18"):  # 保养项目
                sql = "select fbillno,fmemo from s_tc_mmitem_head where 1=1 order by fbillno"
            if (flag == "19"):  # 故障等级
                sql = "select ffaultno,ffaultname from s_ts_fault_level where 1=1  order by ffaultno"
            if (flag == "20"):  # 紧急程度
                sql = "select furgentno,furgentname from s_ts_fault_danger where 1=1  order by furgentno"
            if (flag == "21"):  # 备件分类
                sql = "select fclassstno,fclassstname from s_ts_part_class where 1=1  order by fclassstno"
            if (flag == "22"):  # 设备状态
                sql = "select fstateno,fstatename from s_ts_equipment_state where 1=1 order by fstateno"
            if (flag == "25"):  # 设备类型指令模板
                sql = "select faction,fdescribe from s_ts_equipment_action where 1=1 "
                if arg1 != "":
                    sql = sql + " and fdictno= :arg1"
                sql = sql + " order by faction"
            if (flag == "28"):  # 物料存储分类
                sql = "select fdictno,fdictname from s_ts_dict where fdicttype=3001 order by fdictno"
            if (flag == "29"):  # 客户物料分类
                sql = "select fclassstno,fclassstname from lims.i_ts_good_class where 1=1 and fsite_no='" + arg1 + "' order by fclassstno"
            if (flag == "30"):  # 客户仓库
                sql = "select fstockno,fstockname from lims.i_ts_stock where 1=1 "
                if arg1 != "":
                    sql = sql + " and fsite_no=:arg1"
                sql = sql + " order by fstockno"
            if (flag == "31"):  # 客户物料
                sql = "select fgoodno,fgoodname from lims.i_ts_good_info where 1=1"
                if arg1 != "":
                    sql = sql + " and fsite_no = :arg1"
                sql = sql + " order by fgoodno"
            if (flag == "34"):  # 供应商
                sql = "select fdictno,fdictname from s_ts_dict where fdicttype=" + str(arg1) + " order by fdictno"
            if (flag == "2017"):
                sql = "select fdictno,fdictname from s_ts_dict where fdicttype=2017 order by fdictno"
            if (flag == "27"):
                sql = "select * from lims.s_tc_mmitem_head where feuipmentid = " + str(arg1) + " and fmmlevelno=" + str(
                    arg2)
                q1_data = db.execute(text(sql), param).fetchall()
                if q1_data is not None and len(q1_data) > 0:
                    fbillno = q1_data[0][0]
                    param["fbillno"] = fbillno
                    sql2 = "select * from lims.s_tc_mmitem_detail where fbillno= :fbillno "
                    q2_data = db.execute(text(sql2), param).fetchall()
                    result_data = []
                    if q2_data is not None and len(q2_data) > 0:
                        for key in range(len(q2_data)):
                            value = q2_data[key][2]
                            label = q2_data[key][3]
                            item = {"label": label, "value": value}
                            result_data.append(item)
                        return BaseResponse.success(data=result_data)
                    return BaseResponse.error(message="data not found")
                else:
                    return BaseResponse.error(message="data not found")
            data = db.execute(text(sql), param).fetchall()
            data_list = []
            if data is not None:
                for key in range(len(data)):
                    d1 = {}
                    d1['value'] = int(data[key][0])
                    d1['label'] = data[key][1]
                    data_list.append(d1)
            else:
                logging.error(f"sql查询结果为空:{sql}")
            info_dict = {"list": data_list}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class FbillnoSearch(BaseModel):
    flag: int = Field(None, description="标记值")


@data_dist_router.get("/getFbillno", summary="获取单据编号接口")
def get_fbillno(search: FbillnoSearch = Query(), db: Session = Depends(get_db)):
    try:
        flag = search.flag
        with db.begin():
            sql = "select * from fun_billnumber( :flag )"
            data = db.execute(text(sql), {
                "flag": flag
            }).fetchall()
            fbillno = ""
            if data is not None:
                fbillno = data[0][0]
            return BaseResponse.success(data=fbillno)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_dist_router.get("/sTsEquipmentDictOptions", summary="设备类型接口")
def get_list(db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fdictno,fdictname from s_ts_equipment_dict where 1=1 order by fdictno"
            data = db.execute(text(sql)).fetchall()
            data_list = []
            for key in range(len(data)):
                d1 = {}
                d1['value'] = data[key][0]
                d1['label'] = data[key][1]
                data_list.append(d1)

            info_dict = {"list": data_list}
            return BaseResponse.success(data=info_dict)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class AnySearch(BaseModel):
    valueColumn: str = Field(..., description="列名1")
    labelColumn: str = Field(..., description="列名2")
    tableName: str = Field(..., description="表名")
    intArgColumn: str = Field(..., description="条件")
    intArg: str = Field(..., description="值")


@data_dist_router.get("/getAnyList", summary="数据字典接口")
def get_any_list(any: AnySearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select :valueColumn , :labelColumn from :tableName "
            sql = sql + " where 1=1"
            sql = sql + " and :intArgColumn = :intArg "
            data = db.execute(text(sql), {
                "valueColumn": any.valueColumn,
                "labelColumn": any.labelColumn,
                "tableName": any.tableName,
                "intArgColumn": any.intArgColumn,
                "intArg": any.intArg
            }).fetchall()

            data_list = []
            for key in range(len(data)):
                d1 = {}
                d1['value'] = data[key][0]
                d1['label'] = data[key][1]
                data_list.append(d1)

            info_dict = {"list": data_list}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class FequipmentBarcodeSearch(BaseModel):
    feuipmentid: str = Field(..., description="设备编号")


@data_dist_router.get("/getFequipmentbarcode", summary="获取设备序列号接口")
def get_fequipmentbarcode(search: FequipmentBarcodeSearch = Query(), db: Session = Depends(get_db)):
    try:
        flag = search.feuipmentid
        with db.begin():
            sql = "select * from  fun_barcode( :flag )"
            data_2000 = db.execute(text(sql), {"flag": flag}).fetchall()
            fbillno = ""
            if data_2000:
                fbillno = data_2000[0][0]
            return BaseResponse.success(data=fbillno)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class FmmlevelSearch(BaseModel):
    siteNo: str = Field(None, description="地址编号")
    feuipmentid: int = Field(-1, description="设备编号")
    fmmtypeno: int = Field(-1, description="设备类型")


@data_dist_router.get("/getFmmlevel", summary="获取等级")
def get_fmmlevel(fmmlevel: FmmlevelSearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            param = {}
            sql = "select * from lims.s_ts_equipment_mmset where 1=1  "
            if fmmlevel.siteNo is not None:
                sql = sql + " and fsite_no = :fsiteNo "
                param["fsiteNo"] = fmmlevel.siteNo
            if fmmlevel.feuipmentid != -1:
                sql = sql + " and feuipmentid = :feuipmentid "
                param["feuipmentid"] = fmmlevel.feuipmentid
            if fmmlevel.fmmtypeno != -1:
                sql = sql + " and fmmtypeno = :fmmtypeno "
                param["fmmtypeno"] = fmmlevel.fmmtypeno
            resultData = db.execute(text(sql), param).fetchall()
            if resultData is not None and len(resultData) > 0:
                fmmlevvelno = resultData[0][4]
                sql2 = "select * from lims.s_ts_maintain_level where fmmlevelno = :fmmlevvelno"
                param["fmmlevvelno"] = fmmlevvelno
                resultData2 = db.execute(text(sql2), param).fetchall()
                if resultData2 is not None and len(resultData2) > 0:
                    fmmlevelno = resultData2[0][0]
                    fmmlevelname = resultData2[0][1]
                    fmemo = resultData2[0][2]
                    fupddate = resultData2[0][3]
                    foperator = resultData2[0][4]
                    item = {"fmmlevelno": fmmlevelno, "fmmlevelname": fmmlevelname, "fmemo": fmemo,
                            "fupddate": fupddate,
                            "foperator": foperator}
                    return BaseResponse.success(data=item)
            return BaseResponse.error(message="没有数据")
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
